﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace WeSLog.util
{
    public class DB
    {

        public static DataTable searchAddress(string add)
        {
            DataTable dt = null;
            string queryString = "select distinct StreetName,ZipCode,City from [GEOADR] where StreetName like '" + add + "%'";
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            dt = new DataTable();
                            dt.Load(dr);
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {

            }
            return dt;
        }

        //--------

        public static DataTable getHouseNumber(int houseNumber,string streetName,string zipcode, string city )
        {
            DataTable dt = null;
            string queryString = "select HouseNr,HouseChr from [GEOADR] where HouseNr like '" + houseNumber + "%'" + " and StreetName='" + streetName + "' and Zipcode='" + zipcode + "' and City='" + city +"' ";
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(queryString, cn))
                    {
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            dt = new DataTable();
                            dt.Load(dr);
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {

            }
            return dt;
        }
        //-----------

        //public static string searchCityByZipCode(int zipcode)
        //{
        //    string result = "";
        //    string queryString = "select City from [ADDRESS] where ZipCode = '" + zipcode + "'";
        //    try
        //    {
        //        using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
        //        {
        //            cn.Open();
        //            using (SqlCommand cmd = new SqlCommand(queryString, cn))
        //            {
        //                using (SqlDataReader dr = cmd.ExecuteReader())
        //                {
        //                    dt = new DataTable();
        //                    dt.Load(dr);
        //                }
        //            }
        //            cn.Close();
        //        }
        //    }
        //    catch (Exception ex)
        //    {

        //    }
        //    return dt;
        //}
        



        //--------------------------------
            //public static bool connect()
            //{
            //    bool result = true;
            //    try
            //    {
            //        SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString);
            //        SqlConnection cn.Open();
            //    }
            //    catch
            //    {
            //        result = false;
            //    }
            //    return result;
            //}

            public static string getValue(string queryString)
            {
                string result = "";
                try
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            Object obj = cmd.ExecuteScalar();
                            if (obj == null)
                                result = "";
                            else
                                result = obj.ToString();
                        }
                        cn.Close();
                    }
                }
                catch (Exception ex)
                {

                }

                return result;
            }
            //public static string getValue(string queryString, object[,] pararmeter)
            //{
            //    string result = "";
            //    try
            //    {
            //        using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
            //        {
            //            cn.Open();
            //            using (SqlCommand cmd = new SqlCommand(queryString, cn))
            //            {
            //                if (pararmeter != null)
            //                    for (int i = 0; i < pararmeter.GetLength(0); i++)
            //                        cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
            //                Object obj = cmd.ExecuteScalar();
            //                if (obj == null)
            //                    result = "";
            //                else
            //                    result = obj.ToString();
            //            }
            //            cn.Close();
            //        }

            //    }
            //    catch (Exception ex)
            //    {
            //    }
            //    return result;
            //}

            public static DataTable getData(string queryString)
            {
                DataTable dt = null;
                try
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            using (SqlDataReader dr = cmd.ExecuteReader())
                            {
                                dt = new DataTable();
                                dt.Load(dr);
                            }
                        }
                        cn.Close();
                    }
                }
                catch (Exception ex)
                {

                }
                return dt;
            }

            public static DataTable getData(string queryString, object[,] pararmeter)
            {
                DataTable dt = null;
                try
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            if (pararmeter != null)
                                for (int i = 0; i < pararmeter.GetLength(0); i++)
                                    cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
                            using (SqlDataReader dr = cmd.ExecuteReader())
                            {
                                dt = new DataTable();
                                dt.Load(dr);
                            }
                        }
                        cn.Close();
                    }
                }
                catch (Exception ex)
                {

                }
                return dt;
            }

            public static DataTable getData(string queryString, object[,] pararmeter, bool[] isUnicode)
            {
                DataTable dt = null;
                try
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            if (pararmeter != null)
                                for (int i = 0; i < pararmeter.GetLength(0); i++)
                                {
                                    SqlParameter parameter = new SqlParameter();
                                    parameter.ParameterName = pararmeter[i, 0].ToString();
                                    parameter.Direction = ParameterDirection.Input;
                                    parameter.Value = pararmeter[i, 1].ToString().Replace('-', ' ');
                                    if (isUnicode[i] == true)
                                        parameter.SqlDbType = SqlDbType.NVarChar;
                                    else
                                        parameter.SqlDbType = SqlDbType.VarChar;
                                    cmd.Parameters.Add(parameter);
                                }
                            using (SqlDataReader dr = cmd.ExecuteReader())
                            {
                                dt = new DataTable();
                                dt.Load(dr);
                            }
                        }
                        cn.Close();
                    }
                }
                catch (Exception ex)
                {

                }
                return dt;
            }

            public static bool exec(string queryString)
            {
                bool success = true;
                try
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                        }
                        cn.Close();
                    }
                }
                catch
                {
                    success = false;
                }
                return success;
            }

            public static bool exec(string queryString, object[,] pararmeter)
            {
                bool success = true;
                try
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            cmd.CommandType = CommandType.Text;
                            if (pararmeter != null)
                                for (int i = 0; i < pararmeter.GetLength(0); i++)
                                    cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
                            cmd.ExecuteNonQuery();
                        }
                        cn.Close();
                    }
                }
                catch
                {
                    success = false;
                }
                return success;
            }

            public static bool exec(string queryString, string commandType)
            {
                bool success = true;
                try
                {

                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            switch (commandType)
                            {
                                case "StoredProcedure":
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    break;
                                case "TableDirect":
                                    cmd.CommandType = CommandType.TableDirect;
                                    break;
                                case "Text":
                                    cmd.CommandType = CommandType.Text;
                                    break;
                            }
                            cmd.ExecuteNonQuery();
                        }
                        cn.Close();
                    }
                }
                catch
                {
                    success = false;
                }
                return success;
            }

            public static bool exec(string queryString, object[,] pararmeter, string commandType)
            {
                bool success = true;
                try
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(queryString, cn))
                        {
                            switch (commandType)
                            {
                                case "StoredProcedure":
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    break;
                                case "TableDirect":
                                    cmd.CommandType = CommandType.TableDirect;
                                    break;
                                case "Text":
                                    cmd.CommandType = CommandType.Text;
                                    break;
                            }
                            if (pararmeter != null)
                                for (int i = 0; i < pararmeter.GetLength(0); i++)
                                    cmd.Parameters.Add(new SqlParameter(pararmeter[i, 0].ToString(), pararmeter[i, 1]));
                            cmd.ExecuteNonQuery();
                        }
                        cn.Close();
                    }
                }
                catch
                {
                    success = false;
                }
                return success;
            }
        }
}